REALdatabase Class

The REALdatabase class provides access to the REALdatabase data source, a.k.a., database engine or database back-end that was used in REALbasic 5.5. This class is obsolete and included for compatibility reasons and for users who need to deploy a database on Mac OS "classic." The REALSQLdatabase class is recommended for all other uses.

Events

None

Properties

Methods

More information available in parent classes: Database:Object

Notes

REALdatabase tables may be added, dropped, or modified in various ways (columns or indexes added or dropped) without losing or recopying the existing data.

The new REALdatabase supports a subset of SQL/92 and SQL/99 (details below), including queries that involve self-joins, aggregate functions, and more. For the set of features the new REALdatabase engine supports, its syntax is fully SQL compliant (with a few minor extensions, like the Boolean data type). It also returns standard SQL error codes.

Every new REALdatabase table has a special identifier column called "_rowid" which is a unique integer identifier for that row. The identifier is added automatically and serves as a convenient join field for building relational databases.

A call to SQLSelect returns a dynamic RecordSet; you can move forward, backward, or jump to the beginning or end as much as you like.

The REALdatabase engine supports transactions, both for schema changes and for data changes. A transaction is started automatically when you make any change to the database and is ended by calling either the Commit or Rollback methods of the Database class.

Result Codes

The REALdatabase engine sets the Database class's Error flag after each operation and returns values in the ErrorCode and ErrorMessage properties. When the Error flag is False, the ErrorCode is "0" and the ErrorMessage is "Success". If the Error flag is True, the following codes and messages are returned in ErrorMessage. The SQL Error code appears in brackets following the text of the message.

SQL Error Code	Message
02000	No records found.
01004	Warning: String truncated on right.
01S09	Warning: Invalid SQL keyword.
07000	Error: Dynamic SQL error.
42S01	Error: Table already exists.
42S02	Error: Table not found.
42S11	Error: Index already exists.
42S12	Error: Index not found.
42S21	Error: Column already exists.
42S22	Error: Column not found.
21S01	Error: Insert value list does not match column list.
22000	Error: Data exception.
22023	Error: Invalid parameter.
25000	Error: Invalid transaction state.
0A000	Error: Unsupported SQL feature.
72000	Error: Internal SQL processor error.

Data Types

The REALdatabase engine supports the following data types:

Type	Code	Comments
Integer	3	32-bit signed integer.
VarChar	5	Text fields in the REALdatabase store text encoding information as well as the text itself. When you insert records, the encoding of the text fields is stored along with the contents of the fields. When you retrieve the data, the encoding is restored. Text up to about two billion bytes.
Double	7	64-bit floating-point number.
Date	8	Day, in YYYY-MM-DD format. The Date class's SQLDate property supports this format.
Time	9	Time, in HH:MM:SS format.
TimeStamp	10	Time stamp, in YYYY-MMM-DD HH:MM:SS format. The Date class's SQLDateTime property supports this format.
Boolean	12	Boolean is not a SQL data type and TRUE or FALSE are not SQL keywords. These are REALbasic extensions to SQL. Boolean value, TRUE or FALSE.
Binary	14	Binary data up to about two billion bytes
String	18	Text up to about two billion bytesa.

Creating a REALdatabase

A REALdatabase can be created via the REALdatabase class as shown in the first example.

No table can be greater than 2 GB total size. When storing files in a VirtualVolume, the entire database must also be under 2 GB. Row IDs increment automatically and are never reused; so you can't insert more than about 4 billion records into any table.

Examples

The following example creates a new REALdatabase:

Dim db as REALdatabase
Dim f as FolderItem
f= New FolderItem("mydb")
db= New REALdatabase
db.databaseFile=f
If db.CreateDatabaseFile then
//proceed with database operations...
else
MsgBox "Database not created"
end if

The following example opens an existing REALdatabase.

Dim dbFile as FolderItem
Dim db as REALdatabase
db= New REALdatabase
dbFile = GetFolderItem("Pubs")
db.DatabaseFile=dbFile
If db.Connect() then
//proceed with database operations here..
else
Beep
MsgBox "The database couldn't be opened."
end if

The following example adds a record to a table.

Dim dbFile as FolderItem
Dim db as REALdatabase
db= New REALdatabase
Dim rs As New RecordSet
dbFile= New FolderItem("Employees")
db.databaseFile=dbFile
if db.error then
MsgBox  db.errormessage
else
  db.sqlexecute ("Insert into Employees (Name,Job,YearJoined) Values " _
        +"('Dr.Strangelove','Advisor',1962)")
If db.error then
   MsgBox db.errormessage
else
  db.Commit
end if
end if